﻿#region -- 版 本 注 释 --
/****************************************************
* 文 件 名：
* Copyright(c) 王树羽
* CLR 版本: 4.5
* 创 建 人：王树羽
* 电子邮箱：674613047@qq.com
* 官方网站：https://www.cnblogs.com/shuyu
* 创建日期：2018-06-25 
* 文件描述：
******************************************************
* 修 改 人：
* 修改日期：
* 备注描述：
*******************************************************/
#endregion

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace CommonLib.DbHelper
{
    /// <summary>
    /// MsSqlServer 数据库帮助类
    /// </summary>
    public class MsSqlServer : IDBhelper
    {
        /// <summary>
        /// 数据库上下文对象
        /// </summary>
        public DbHelper.DbContext Context { get; set; }

        #region MS Sql Server IDataReader

        /// <summary>
        /// 返回 IDataReader
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <returns>返回IDataReader</returns>
        public virtual IDataReader ExecuteReader(string sqlStr)
        {
            SqlConnection Conn = new SqlConnection(this.Context.ConnString);
            SqlCommand Comm = new SqlCommand(sqlStr, Conn);
            Conn.Open();
            return (IDataReader)Comm.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 返回IDataReader
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>

        /// <returns>返回IDataReader</returns>
        public virtual IDataReader ExecuteReader(string sqlStr, object Params)
        {
            SqlConnection Conn = new SqlConnection(this.Context.ConnString);
            Conn.Open();
            SqlCommand Comm = new SqlCommand(sqlStr, Conn);
            if (Params != null)
            {
                Comm.Parameters.Add(Params);
            }
            IDataReader sdr = Comm.ExecuteReader(CommandBehavior.CloseConnection);
            Comm.Parameters.Clear();
            return (IDataReader)sdr;
        }
        /// <summary>
        /// 返回 IDataReader
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>

        /// <returns>返回值 IDataReader类型的参数</returns>
        public virtual IDataReader ExecuteReader(string sqlStr, object[] Params)
        {
            SqlConnection Conn = new SqlConnection(this.Context.ConnString);
            Conn.Open();
            SqlCommand Comm = new SqlCommand(sqlStr, Conn);
            if (Params != null)
            {
                foreach (object P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }
            IDataReader sdr = Comm.ExecuteReader(CommandBehavior.CloseConnection);
            Comm.Parameters.Clear();
            return (IDataReader)sdr;
        }

        #endregion IDataReader

        #region MS Sql Server DataTable

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sqlStr">sql语句</param>

        /// <returns></returns>
        public virtual DataTable ExecuteTable(string sqlStr)
        {
            SqlConnection Conn = new SqlConnection(this.Context.ConnString);
            SqlDataAdapter Da = new SqlDataAdapter(sqlStr, Conn);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Da.Dispose();
            }
        }

        /// <summary>
        /// 返回 DataTable
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>

        /// <returns>返回值 DataTable类型的参数</returns>
        public virtual DataTable ExecuteTable(string sqlStr, object Params)
        {
            SqlConnection Conn = new SqlConnection(this.Context.ConnString);
            SqlCommand Comm = new SqlCommand(sqlStr, Conn);
            if (Params != null)
            {
                Comm.Parameters.Add(Params);
            }
            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }

        /// <summary>
        /// 返回 DataTable
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <returns>返回值 DataTable类型的参数</returns>
        public virtual DataTable ExecuteTable(string sqlStr, object[] Params)
        {
            SqlConnection Conn = new SqlConnection(this.Context.ConnString);
            SqlCommand Comm = new SqlCommand(sqlStr, Conn);
            if (Params != null)
            {
                foreach (object P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }
            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                return Dt;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }

        /// <summary>
        /// 分页存储过程 返回DataTable
        /// </summary>
        /// <param name="total">输出参数 总数量</param>
        /// <param name="tbName">表名(多表逗号隔开)</param>
        /// <param name="Fields">字段列表</param>
        /// <param name="OrderBy">排序</param>
        /// <param name="PageSize">每页数量</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="Where">where条件</param>
        /// <param name="Params">参数</param>
        /// <returns></returns>
        public virtual DataTable ExecuteTablePage(ref int total, string tbName, string Fields, string OrderBy, int PageSize, int PageIndex, string Where, object[] Params)
        {
            SqlConnection Conn = new SqlConnection(this.Context.ConnString);
            SqlCommand Comm = new SqlCommand("page", Conn);
            Comm.CommandType = CommandType.StoredProcedure;

            Comm.Parameters.Add(new SqlParameter("@tbName", tbName));
            if (Fields != "")
            {
                Comm.Parameters.Add(new SqlParameter("@Fields", Fields));
            }
            if (OrderBy != "")
            {
                Comm.Parameters.Add(new SqlParameter("@OrderBy", OrderBy));
            }
            if (Where != "")
            {
                Comm.Parameters.Add(new SqlParameter("@Where", Where));
            }
            if (PageSize > 0)
            {
                Comm.Parameters.Add(new SqlParameter("@PageSize", PageSize));
            }
            if (PageIndex > 0)
            {
                Comm.Parameters.Add(new SqlParameter("@PageIndex", PageIndex));
            }

            SqlParameter para = new SqlParameter("@total", SqlDbType.Int, 20);//定义output参数类型
            para.Direction = ParameterDirection.Output;//设置这个参数的类型是输出参数
            Comm.Parameters.Add(para);

            if (Params != null)
            {
                foreach (object P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }

            SqlDataAdapter Da = new SqlDataAdapter(Comm);
            try
            {
                DataTable Dt = new DataTable();
                Da.Fill(Dt);
                string t = Comm.Parameters["@total"].Value.ToString();
                total = Convert.ToInt32(Comm.Parameters["@total"].Value.ToString());
                return Dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                Comm.Parameters.Clear();
                Da.Dispose();
                Conn.Close();
            }
        }
        #endregion

        #region MS Sql Server ExecuteScalar

        /// <summary>
        /// 返回sql语句的查询结果
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>返回的是一个字符串</returns>
        public virtual string ExecuteScalar(string sqlStr)
        {
            try
            {
                string i = "";
                IDataReader sdr = ExecuteReader(sqlStr);
                if (sdr.Read())
                {
                    i = sdr[0].ToString();
                }
                sdr.Close();
                return i;
            }
            catch
            {
                HttpContext.Current.Response.Write(sqlStr);
                HttpContext.Current.Response.End();
            }
            return "";
        }

        /// <summary>
        /// 返回 object
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        public virtual string ExecuteScalar(string sqlStr, object Params)
        {
            try
            {
                string i = "";
                IDataReader sdr = ExecuteReader(sqlStr, Params);
                if (sdr.Read())
                {
                    i = sdr[0].ToString();
                }
                sdr.Close();
                return i;
            }
            catch
            {
                HttpContext.Current.Response.Write(sqlStr);
                HttpContext.Current.Response.End();
            }
            return null;
        }

        /// <summary>
        /// 返回 object
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        public virtual string ExecuteScalar(string sqlStr, object[] Params)
        {
            try
            {
                string i = "";
                IDataReader sdr = ExecuteReader(sqlStr, Params);
                if (sdr.Read())
                {
                    i = sdr[0].ToString();
                }
                sdr.Close();
                return i;
            }
            catch
            {
                HttpContext.Current.Response.Write(sqlStr);
                HttpContext.Current.Response.End();
            }
            return null;
        }
        #endregion ExecuteScalar

        #region MS Sql Server ExecuteScalars

        /// <summary>
        /// 返回查询的第一个字段值
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <returns></returns>
        public virtual string ExecuteScalars(string sqlStr)
        {
            try
            {
                StringBuilder str = new StringBuilder(); string ls = "";
                IDataReader sdr = ExecuteReader(sqlStr);
                while (sdr.Read())
                {
                    str.Append(ls + sdr[0].ToString());
                    ls = ",";
                }
                sdr.Close();
                return str.ToString();
            }
            catch
            {
                HttpContext.Current.Response.Write(sqlStr + "<br />");
            }
            return null;
        }

        /// <summary>
        /// 返回查询的第一个字段值,逗号隔开
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">参数列表</param>
        /// <returns></returns>
        public virtual string ExecuteScalars(string sqlStr, object Params)
        {
            StringBuilder str = new StringBuilder(); string ls = "";
            IDataReader sdr = ExecuteReader(sqlStr, Params);
            while (sdr.Read())
            {
                str.Append(ls + sdr[0].ToString());
                ls = ",";
            }
            sdr.Close();
            return str.ToString();
        }
        /// <summary>
        /// 返回查询的第一个字段值,逗号隔开
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">参数列表</param>
        /// <returns></returns>
        public virtual string ExecuteScalars(string sqlStr, object[] Params)
        {
            StringBuilder str = new StringBuilder(); string ls = "";
            IDataReader sdr = ExecuteReader(sqlStr, Params);
            while (sdr.Read())
            {
                str.Append(ls + sdr[0].ToString());
                ls = ",";
            }
            sdr.Close();
            return str.ToString();
        }

        #endregion ExecuteScalars

        #region MS Sql Server ExecuteNonQuery

        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="sqlStr">sql语句</param> 
        /// <returns></returns>
        public virtual int ExecuteNonQuery(string sqlStr)
        {
            SqlCommand Comm = SetSqlCommand(sqlStr);

            var i = Comm.ExecuteNonQuery();
            try
            {
                if (this.Context.Trans.Connection == null)
                {
                    Comm.Dispose();
                    this.Context.Conn.Close();
                }
            }
            catch
            {
            }
            return i;
        }

        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        public virtual int ExecuteNonQuery(string sqlStr, object Params)
        {
            #region 注释

            //SqlCommand Comm = SetSqlCommand(sqlStr, Params);

            //if (this.Trans != null)
            //{
            //    return Comm.ExecuteNonQuery();
            //}
            //try
            //{
            //    return Comm.ExecuteNonQuery();
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    Comm.Dispose();
            //    Conn.Close();
            //} 
            #endregion

            SqlCommand Comm = SetSqlCommand(sqlStr, Params);

            var i = Comm.ExecuteNonQuery();
            try
            {
                if (this.Context.Trans.Connection == null)
                {
                    Comm.Dispose();
                    this.Context.Conn.Close();
                }
            }
            catch
            {
            }
            return i;
        }

        /// <summary>
        /// 执行 ExecuteNonQuery
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="Params">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        public virtual int ExecuteNonQuery(string sqlStr, object[] Params)
        {
            #region 注释

            //SqlCommand Comm = SetSqlCommand(sqlStr, Params);

            //if (this.Trans != null)
            //{
            //    return Comm.ExecuteNonQuery();
            //}
            //try
            //{
            //    return Comm.ExecuteNonQuery();
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    Comm.Dispose();
            //    Conn.Close();
            //} 
            #endregion
            try
            {
                SqlCommand Comm = SetSqlCommand(sqlStr, Params);
                int i = 0;

                i = Comm.ExecuteNonQuery();

                if (this.Context.Trans == null)
                {
                    Comm.Dispose();
                    this.Context.Conn.Close();
                }
                else if (this.Context.Trans.Connection == null)
                {
                    Comm.Dispose();
                    this.Context.Conn.Close();
                }
                return i;
            }
            catch (Exception ex)
            {
                string str = ex.Message;
                throw;
            }
        }
        #endregion ExecuteNonQuery  

        #region MS Sql Server 添加SqlParameter参数

        /// <summary>
        /// 添加 mssql 参数话查询 参数
        /// </summary>
        /// <param name="ParamName">参数名 @名称 </param>
        /// <param name="sqlType">参数类型</param>
        /// <param name="Size">参数的长度</param>
        /// <param name="value">参数的值</param>
        /// <returns>返回 SqlParameter 对象</returns>
        public virtual DbParameter AddParam(string ParamName, SqlDbType sqlType, int Size, object value)
        {
            SqlParameter param = new SqlParameter(ParamName, sqlType, Size);
            param.Value = value;
            return param;
        }

        /// <summary>
        /// 添加 mssql 参数化查询 参数
        /// </summary>
        /// <param name="ParamName">参数名 @名称</param>
        /// <param name="value">参数的值</param>
        /// <returns>返回 SqlParameter 对象</returns>
        public virtual DbParameter AddParam(string ParamName, object value)
        {
            return new SqlParameter(ParamName, value);
        }

        #endregion MS Sql Server 添加SqlParameter参数

        #region SetSqlCommand

        private SqlCommand SetSqlCommand(string sqlStr, object Params = null)
        {
            SqlCommand Comm = new SqlCommand(sqlStr);
            if (Params != null)
            {
                Comm.Parameters.Add(Params);
            }

            if (this.Context.Trans != null)//开启事务
            {
                Comm.Transaction = this.Context.Trans;
            }
            else
            {
                OpenConn();
            }
            Comm.Connection = this.Context.Conn;
            return Comm;
        }
        private SqlCommand SetSqlCommand(string sqlStr, object[] Params)
        {
            SqlCommand Comm = new SqlCommand(sqlStr);
            if (Params != null)
            {
                foreach (var P in Params)
                {
                    Comm.Parameters.Add(P);
                }
            }

            if (this.Context.Trans != null)//开启事务
            {
                Comm.Transaction = this.Context.Trans;
            }
            else
            {
                OpenConn();
            }
            Comm.Connection = this.Context.Conn;
            return Comm;
        }

        #endregion

        #region Connection

        private void OpenConn()
        {
            if (this.Context.Conn == null)
            {
                this.Context.Conn = new SqlConnection(this.Context.ConnString);
            }
            if (this.Context.Conn.State == ConnectionState.Closed)
            {
                this.Context.Conn.Open();
            }
        }

        private void CloseConn()
        {
            if (this.Context.Conn == null)
            {
                return;
            }
            if (this.Context.Conn.State == ConnectionState.Open)
            {
                this.Context.Conn.Close();
            }
            this.Context.Conn.Dispose();
            this.Context.Conn = null;
        }
        #endregion
    }
}
